SQL tips¶

テストのためのテーブル差分確認用のSQL¶

なぜSQLで行うか¶

exportしてから行うのはめんどう
exportする場合、事前にsortする必要がある
データ量が多くてexportもdiffとるのも大変

select "a-b", * from actual_table
except
select "a-b", * from expected_table
union all
select "b-a", * from expected_table
except
select "b-a", * from actual_table

横持ちを縦持ちに¶

逆はよくやるけど、こちらはあまりやらないので忘れてしまうためメモ

よくある横持ちを縦持ちにするクエリ
M人のN種類のサマった結果（M * N）の表を
種類別のレコードM * N行に変換する
ただし、IDとサマった結果に加えて種類の列が追加される（M*N * 3）

create table yoko
(
    id int
    , a_cnt int
    , b_cnt int
    , c_cnt int
)
;
insert into yoko values
(1, 1, 2, 3)
, (2, 4, 5, 6)
, (3, 7, 8, 9)
;
create table names
(
    id int
    , name varchar
)
;
insert into names values
(1, 'a'), (2, 'b'), (3, 'c')
;
-- yoko to tate
select
    yoko.id
    , p.id
    , p.name
    , case
        when p.id = 1 then yoko.a_cnt
        when p.id = 2 then yoko.b_cnt
        when p.id = 3 then yoko.c_cnt
        else null
    end as cnt
from yoko
cross join names  as p

下記の各種cntをIDによって使うものを変えるだけ

| id | a_cnt | b_cnt | c_cnt | id | name | |—-|——-|——-|——-|—-|——| | 1 | 1 | 2 | 3 | 1 | a | | 2 | 4 | 5 | 6 | 1 | a | | 3 | 7 | 8 | 9 | 1 | a | | 1 | 1 | 2 | 3 | 2 | b | | 2 | 4 | 5 | 6 | 2 | b | | 3 | 7 | 8 | 9 | 2 | b | | 1 | 1 | 2 | 3 | 3 | c | | 2 | 4 | 5 | 6 | 3 | c | | 3 | 7 | 8 | 9 | 3 | c |

pandas version¶

https://gist.github.com/ksomemo/bc9b336ab7e0aa604eae

with 複数¶

with aaa as (
  select 1 as a
)
, bbb as (
  select a, 1 as b
  from aaa
)
select a, b, 1 as c
from bbb

-- a b c
-- 1 1 1